import sqlite3
import os
from pathlib import Path
from datetime import datetime
from config import Config

class Database:
    def __init__(self, db_path=None):
        self.db_path = db_path or Config.DATABASE_PATH
        self.init_database()
    
    def init_database(self):
        """初始化数据库，创建必要的表"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # 创建底板裁片表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS templates (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL UNIQUE,
                    width INTEGER NOT NULL,
                    height INTEGER NOT NULL,
                    description TEXT,
                    image_path TEXT,
                    original_filename TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # 创建定制区域表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS regions (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    template_id INTEGER NOT NULL,
                    name TEXT NOT NULL,
                    x INTEGER NOT NULL,
                    y INTEGER NOT NULL,
                    width INTEGER NOT NULL,
                    height INTEGER NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (template_id) REFERENCES templates (id) ON DELETE CASCADE
                )
            ''')
            
            # 创建项目表（用于保存定制项目）
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS projects (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL,
                    template_id INTEGER NOT NULL,
                    json_config TEXT,
                    export_path TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (template_id) REFERENCES templates (id)
                )
            ''')
            
            conn.commit()
    
    def execute_query(self, query, params=None, fetch=False):
        """执行数据库查询"""
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row  # 使结果可以通过列名访问
            cursor = conn.cursor()
            
            if params:
                cursor.execute(query, params)
            else:
                cursor.execute(query)
            
            if fetch:
                if fetch == 'one':
                    return cursor.fetchone()
                else:
                    return cursor.fetchall()
            
            conn.commit()
            return cursor.lastrowid

# 数据访问层类
class TemplateDAO:
    def __init__(self, db):
        self.db = db
    
    def create_template(self, name, width, height, description=None, image_path=None, original_filename=None):
        """创建新的底板裁片"""
        query = '''
            INSERT INTO templates (name, width, height, description, image_path, original_filename)
            VALUES (?, ?, ?, ?, ?, ?)
        '''
        return self.db.execute_query(query, (name, width, height, description, image_path, original_filename))
    
    def get_all_templates(self):
        """获取所有底板裁片"""
        query = 'SELECT * FROM templates ORDER BY created_at DESC'
        return self.db.execute_query(query, fetch='all')
    
    def get_template_by_id(self, template_id):
        """根据ID获取底板裁片"""
        query = 'SELECT * FROM templates WHERE id = ?'
        return self.db.execute_query(query, (template_id,), fetch='one')
    
    def update_template(self, template_id, name, width, height, description=None, image_path=None, original_filename=None):
        """更新底板裁片"""
        if image_path is not None:
            query = '''
                UPDATE templates 
                SET name = ?, width = ?, height = ?, description = ?, image_path = ?, original_filename = ?, updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
            '''
            self.db.execute_query(query, (name, width, height, description, image_path, original_filename, template_id))
        else:
            query = '''
                UPDATE templates 
                SET name = ?, width = ?, height = ?, description = ?, updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
            '''
            self.db.execute_query(query, (name, width, height, description, template_id))
    
    def delete_template(self, template_id):
        """删除底板裁片"""
        query = 'DELETE FROM templates WHERE id = ?'
        self.db.execute_query(query, (template_id,))

class RegionDAO:
    def __init__(self, db):
        self.db = db
    
    def create_region(self, template_id, name, x, y, width, height):
        """创建新的定制区域"""
        query = '''
            INSERT INTO regions (template_id, name, x, y, width, height)
            VALUES (?, ?, ?, ?, ?, ?)
        '''
        return self.db.execute_query(query, (template_id, name, x, y, width, height))
    
    def get_regions_by_template(self, template_id):
        """获取指定底板的所有定制区域"""
        query = 'SELECT * FROM regions WHERE template_id = ? ORDER BY id'
        return self.db.execute_query(query, (template_id,), fetch='all')
    
    def get_region_by_id(self, region_id):
        """根据ID获取定制区域"""
        query = 'SELECT * FROM regions WHERE id = ?'
        return self.db.execute_query(query, (region_id,), fetch='one')
    
    def update_region(self, region_id, name, x, y, width, height):
        """更新定制区域"""
        query = '''
            UPDATE regions 
            SET name = ?, x = ?, y = ?, width = ?, height = ?
            WHERE id = ?
        '''
        self.db.execute_query(query, (name, x, y, width, height, region_id))
    
    def delete_region(self, region_id):
        """删除定制区域"""
        query = 'DELETE FROM regions WHERE id = ?'
        self.db.execute_query(query, (region_id,))

class ProjectDAO:
    def __init__(self, db):
        self.db = db
    
    def create_project(self, name, template_id, json_config=None):
        """创建新项目"""
        query = '''
            INSERT INTO projects (name, template_id, json_config)
            VALUES (?, ?, ?)
        '''
        return self.db.execute_query(query, (name, template_id, json_config))
    
    def get_all_projects(self):
        """获取所有项目"""
        query = '''
            SELECT p.*, t.name as template_name 
            FROM projects p 
            JOIN templates t ON p.template_id = t.id 
            ORDER BY p.created_at DESC
        '''
        return self.db.execute_query(query, fetch='all')
    
    def get_project_by_id(self, project_id):
        """根据ID获取项目"""
        query = '''
            SELECT p.*, t.name as template_name 
            FROM projects p 
            JOIN templates t ON p.template_id = t.id 
            WHERE p.id = ?
        '''
        return self.db.execute_query(query, (project_id,), fetch='one')
    
    def update_project(self, project_id, name=None, json_config=None, export_path=None):
        """更新项目"""
        updates = []
        params = []
        
        if name is not None:
            updates.append('name = ?')
            params.append(name)
        if json_config is not None:
            updates.append('json_config = ?')
            params.append(json_config)
        if export_path is not None:
            updates.append('export_path = ?')
            params.append(export_path)
        
        if updates:
            updates.append('updated_at = CURRENT_TIMESTAMP')
            params.append(project_id)
            
            query = f'UPDATE projects SET {", ".join(updates)} WHERE id = ?'
            self.db.execute_query(query, params)
    
    def delete_project(self, project_id):
        """删除项目"""
        query = 'DELETE FROM projects WHERE id = ?'
        self.db.execute_query(query, (project_id,))

# 全局数据库实例
db = Database()
template_dao = TemplateDAO(db)
region_dao = RegionDAO(db)
project_dao = ProjectDAO(db)
